
# 连表查询
    # 笛卡尔积
    # 先计算两张表的笛卡尔积,再根据用户给出的条件进行筛选
    # select * from employee,department where dep_id = department.id

    # 内连接  inner join ... on 连接条件
        # select * from 表1 inner join 表2 on 条件
        # select * from employee inner join department on dep_id = department.id
            # employee --> dep_id: 200,201,202,204
            # department --> id : 200,201,202,203
        # 内连接 :只显示两张表中互相匹配的项,其他不匹配的不显示
    # 外连接
        # 左外连接 left join .. on
            # select * from 表1 left join 表2 on 条件
            # select * from employee left join department on dep_id = department.id
            # select * from department left join employee  on dep_id = department.id
            # 不管左表中是不是匹配上都会显示所有内容
        # 右外连接 right join .. on
            # select * from 表1 right join 表2 on 条件
            # select * from employee right join department on dep_id = department.id
            # 不管右表中是不是匹配上都会显示所有内容
        # 全外连接
            # select * from department left join employee  on dep_id = department.id
            # union
            # select * from department right join employee on dep_id = department.id

    # 连表查询
    # 原来是两张表,现在拼成一张表,所有的操作都像是操作一张表一样了
    # 年龄大于25岁的员工以及员工所在的部门
    # select * from employee inner join department d on d.id = dep_id;

    # 以内连接的方式查询employee和department表，并且以age字段的升序方式显示
    # select * from employee e inner join department d on d.id = e.dep_id order by age;

# 子查询
    # 存在年龄大于25岁员工的部门
    # select * from employee where age>25;
    # select distinct dep_id from employee where age>25;
    # select * from department where id in (select distinct dep_id from employee where age>25);

    # a.查询平均年龄在25岁以上的部门名
        # 求部门的平均年龄
        # select avg(age) from employee group by dep_id
        # 求部门的平均年龄>25岁的 部门id
        # select dep_id from employee group by dep_id having avg(age)>25
        # 根据部门id求部门名
        # select name from department where id in (select dep_id from employee group by dep_id having avg(age)>25);

    # b.查询平均年龄在25岁以上的部门名,平均年龄的值
        # 方法1:
        # 先查部门的平均年龄>25岁的部门id,平均年龄
        # select dep_id,avg(age) from employee group by dep_id having avg(age) > 25
        # 查出结果在之后再连表
        # select name,avg_age from department as d right join (select dep_id,avg(age) as avg_age from employee group by dep_id having avg(age) > 25) as t on d.id = t.dep_id

        # 方法2
        # 先查各部门的平均年龄
        # select dep_id,avg(age) from employee group by dep_id;
        # 查出结果在之后再连表
        # select name,avg_age from
        #     department as d inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t
        #     on d.id =t.dep_id
        #     where avg_age>25

        # 根据a,b总结出来的规律:
        # 如果最终需要的结果只出现在一张表中,可以用子查询解决问题
        # 如果最终需要的结果出现在2表中,那么最后用的一定是连表查询

    # 查看"技术"部员工姓名
        # 技术部门在department表中的id
        # select id from department where name = '技术';
        # 查的是姓名 employee
        # select name from employee where dep_id = (select id from department where name = '技术');

    # 查看不足1人的部门名
        # 子查询
        # 先查出所有人的部门id
            # select distinct dep_id from employee;
        # 看不在部门id这个集合中的部门有哪些
            # select name from department where id not in (select distinct dep_id from employee);

        # 连表
        # select d.name from department d left join employee e on d.id = dep_id where e.id is null;

    # 查询大于所有人平均年龄的员工名与年龄
        # 先查询所有人的平均年龄
        # select avg(age) from employee;
        # select name,age from employee where age > (select avg(age) from employee);

    # 查询大于部门内平均年龄的员工名、年龄
        # 先求各部门的平均年龄
        # select dep_id,avg(age) from employee group by dep_id;
        # 拼接
        # select e.name,e.age from
            # employee e
        # inner join
            # (select dep_id,avg(age) avg_age from employee group by dep_id) t
        # on e.dep_id = t.dep_id
        # where e.age > t.avg_age;


# select * from 表 where 字段 > 一个值
# 如果我们查询出来的结果是一个值,那么就可以使用 >  <  =
# select * from 表 where 字段 in (1个值,1个值)
# 如果我们查询出来的结果是一列中的多个值
        # dep_id
        # 201
        # 202
        # 204
# 如果我们查出来的是一张表,也不能作为条件,如果必须保留这两个字段,就不能用作条件,只能连表
    # id name
    # 1  alex


'''
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
'''

# 每个部门最新入职的那位员工
    # 每个部门最新入职时间
    # select post,max(hire_date) from emp group by post;
    # 对比 最新入职时间以及部门 和员工表中的入职时间和部门 是不是一致
    # select name,hire_date,emp.post from emp inner join (select post,max(hire_date) as max_date from emp group by post) as t
    # on t.post = emp.post and emp.hire_date = t.max_date;

# select 语句到底做了什么?
#     select name from emp;
#     select name as n,(select age from employee where name = n) from emp;

# 子查询处理可以放在条件中,还可以放在连表中,还可以放在select字段(要求查询的结果必须是一个单行单列的值)中.

# 推荐连表 : 连表的效率比子查询的效率要高